package com.atomcorp.servicio;

import com.atomcorp.modelo.CatalogoDetalle;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;

/**
 *
 * @author Harold
 */
public class CatalogoDetalleServicio {
    
    public ObservableList<CatalogoDetalle> listarCatalogoDetalles(Long idCatalogo, int anio) {        
        ObservableList<CatalogoDetalle> data = FXCollections.observableArrayList();
        try {
            if (Conexion.getConection() != null) {
                String sql = "SELECT * FROM catalogo_detalle WHERE estado = 1 and detalle_padre_id = 0 and anio = " + anio + " and catalogo_id = " + idCatalogo + " ORDER BY detalle_padre_id,id";
                Statement stm = Conexion.getConection().createStatement();
                try (ResultSet rs = stm.executeQuery(sql)) {
                    while (rs.next()) {
                        data.add(new CatalogoDetalle(rs.getLong("ID"), rs.getString("NOMBRE"), rs.getString("NEMONICO"), rs.getString("DESCRIPCION"),
                                rs.getLong("DETALLE_PADRE_ID"), rs.getLong("CATALOGO_ID"), rs.getBoolean("ESTADO"),rs.getDouble("PORCENTAJE"),
                                rs.getDouble("RANGO_MINIMO"),rs.getDouble("RANGO_MAXIMO"),rs.getInt("ANIO"), rs.getDouble("VALOR")));
                    }
                }
                Conexion.getConection().close();
                return data;
            }
        } catch (SQLException e) {
            Logger.getLogger(CatalogoDetalleServicio.class.getName()).log(Level.SEVERE, null, e);
        }
        return null;
    }
    
    public ObservableList<CatalogoDetalle> obtenerHijos(Long idPadre) {        
        ObservableList<CatalogoDetalle> data = FXCollections.observableArrayList();
        try {
            if (Conexion.getConection() != null) {
                String sql = "SELECT * FROM catalogo_detalle WHERE estado = 1 and detalle_padre_id = " + idPadre + " ORDER BY id";
                Statement stm = Conexion.getConection().createStatement();
                try (ResultSet rs = stm.executeQuery(sql)) {
                    while (rs.next()) {
                        data.add(new CatalogoDetalle(rs.getLong("ID"), rs.getString("NOMBRE"), rs.getString("NEMONICO"), rs.getString("DESCRIPCION"),
                                rs.getLong("DETALLE_PADRE_ID"), rs.getLong("CATALOGO_ID"), rs.getBoolean("ESTADO"),rs.getDouble("PORCENTAJE"),
                                rs.getDouble("RANGO_MINIMO"),rs.getDouble("RANGO_MAXIMO"),rs.getInt("ANIO"), rs.getDouble("VALOR")));
                    }
                }
                Conexion.getConection().close();
                return data;
            }
        } catch (SQLException e) {
            Logger.getLogger(CatalogoDetalleServicio.class.getName()).log(Level.SEVERE, null, e);
        }
        return null;
    }
    
    public ObservableList<CatalogoDetalle> obtenerCatDetalleTotal(int anio) {        
        ObservableList<CatalogoDetalle> data = FXCollections.observableArrayList();
        try {
            if (Conexion.getConection() != null) {
                String sql = "SELECT c.id,c.nombre,c.nemonico,c.descripcion,c.detalle_padre_id,c1.nombre AS NOMBRE_PADRE,c.catalogo_id,cat.nombre AS NOMBRE_CATALOGO,"
                        + "c.estado,c.porcentaje,c.rango_minimo,c.rango_maximo,c.anio,c.valor "
                        + "FROM catalogo_detalle AS c LEFT JOIN catalogo_detalle AS c1 ON c.detalle_padre_id = c1.id LEFT JOIN catalogo AS cat ON "
                        + "c.catalogo_id = cat.id "
                        + "WHERE c.estado = 1 AND c.anio = " + anio + " ORDER BY c.id";
                Statement stm = Conexion.getConection().createStatement();
                try (ResultSet rs = stm.executeQuery(sql)) {
                    while (rs.next()) {
                        data.add(new CatalogoDetalle(rs.getLong("ID"), rs.getString("NOMBRE"), rs.getString("NEMONICO"), rs.getString("DESCRIPCION"),
                                rs.getLong("DETALLE_PADRE_ID"), rs.getString("NOMBRE_PADRE"), rs.getLong("CATALOGO_ID"), rs.getString("NOMBRE_CATALOGO"), 
                                rs.getBoolean("ESTADO"),rs.getDouble("PORCENTAJE"),rs.getDouble("RANGO_MINIMO"),rs.getDouble("RANGO_MAXIMO"),
                                rs.getInt("ANIO"), rs.getDouble("VALOR")));
                    }
                }
                Conexion.getConection().close();
                return data;
            }
        } catch (SQLException e) {
            Logger.getLogger(CatalogoDetalleServicio.class.getName()).log(Level.SEVERE, null, e);
        }
        return null;
    }

    public ObservableList<CatalogoDetalle> obtenerCatalogoDetallePorCriterio(String criterio, String valor) {
        ObservableList<CatalogoDetalle> data = FXCollections.observableArrayList();
        try {
            if (Conexion.getConection() != null) {
                String sql = "SELECT c.id,c.nombre,c.nemonico,c.descripcion,c.detalle_padre_id,c1.nombre AS NOMBRE_PADRE,c.catalogo_id,cat.nombre AS NOMBRE_CATALOGO,"
                        + "c.estado,c.porcentaje,c.rango_minimo,c.rango_maximo,c.anio,c.valor "
                        + "FROM catalogo_detalle AS c LEFT JOIN catalogo_detalle AS c1 ON c.detalle_padre_id = c1.id LEFT JOIN catalogo AS cat ON "
                        + "c.catalogo_id = cat.id "
                        + "WHERE c.estado = 1 AND c." + criterio + " LIKE '%" + valor + "%' ORDER BY c.id";
                Statement stm = Conexion.getConection().createStatement();
                try (ResultSet rs = stm.executeQuery(sql)) {
                    while (rs.next()) {
                        data.add(new CatalogoDetalle(rs.getLong("ID"), rs.getString("NOMBRE"), rs.getString("NEMONICO"), rs.getString("DESCRIPCION"),
                                rs.getLong("DETALLE_PADRE_ID"), rs.getString("NOMBRE_PADRE"), rs.getLong("CATALOGO_ID"), rs.getString("NOMBRE_CATALOGO"), 
                                rs.getBoolean("ESTADO"),rs.getDouble("PORCENTAJE"),rs.getDouble("RANGO_MINIMO"),rs.getDouble("RANGO_MAXIMO"),
                                rs.getInt("ANIO"), rs.getDouble("VALOR")));
                    }
                }
                Conexion.getConection().close();
                return data;
            }
        } catch (SQLException e) {
            Logger.getLogger(CatalogoDetalleServicio.class.getName()).log(Level.SEVERE, null, e);
        }
        return null;
    }

    public void ingresar(CatalogoDetalle catalogoDetalle) {
        try {
            if (Conexion.getConection() != null) {
                String sql = " INSERT INTO catalogo_detalle "
                        + " (NOMBRE, NEMONICO, DESCRIPCION, DETALLE_PADRE_ID, CATALOGO_ID, ESTADO, PORCENTAJE, RANGO_MINIMO, RANGO_MAXIMO,"
                        + " ANIO, VALOR) "
                        + " VALUES (?,?,?,?,?,?,?,?,?,?,?) ";
                try (PreparedStatement prep = Conexion.getConection().prepareStatement(sql)) {
                    prep.setString(1, catalogoDetalle.getNombre());
                    prep.setString(2, catalogoDetalle.getNemonico());
                    prep.setString(3, catalogoDetalle.getDescripcion());
                    prep.setLong(4, catalogoDetalle.getDetallePadreId());
                    prep.setLong(5, catalogoDetalle.getCatalogoId());
                    prep.setBoolean(6, catalogoDetalle.getEstado());
                    prep.setDouble(7, catalogoDetalle.getPorcentaje());
                    prep.setDouble(8, catalogoDetalle.getRangoMinimo());
                    prep.setDouble(9, catalogoDetalle.getRangoMaximo());
                    prep.setInt(10, catalogoDetalle.getAnio());
                    prep.setDouble(11, catalogoDetalle.getValor());
                    prep.addBatch();
                    prep.executeBatch();
                    Conexion.getConection().setAutoCommit(true);
                }
                Conexion.getConection().close();
            }
        } catch (SQLException e) {
            Logger.getLogger(CatalogoDetalleServicio.class.getName()).log(Level.SEVERE, null, e);
        }
    }

    public void editar(CatalogoDetalle catalogoDetalle) {
        try {
            if (Conexion.getConection() != null) {
                String sql = " UPDATE catalogo_detalle "
                        + " SET NOMBRE = ?, NEMONICO = ?, DESCRIPCION = ?, DETALLE_PADRE_ID = ?, CATALOGO_ID = ?, ESTADO = ?, "
                        + " PORCENTAJE = ?, RANGO_MINIMO = ?, RANGO_MAXIMO = ?,"
                        + " ANIO = ?, VALOR = ? "
                        + " WHERE ID = ?";
                try (PreparedStatement prep = Conexion.getConection().prepareStatement(sql)) {
                    prep.setString(1, catalogoDetalle.getNombre());
                    prep.setString(2, catalogoDetalle.getNemonico());
                    prep.setString(3, catalogoDetalle.getDescripcion());
                    prep.setLong(4, catalogoDetalle.getDetallePadreId());
                    prep.setLong(5, catalogoDetalle.getCatalogoId());
                    prep.setBoolean(6, catalogoDetalle.getEstado());
                    prep.setDouble(7, catalogoDetalle.getPorcentaje());
                    prep.setDouble(8, catalogoDetalle.getRangoMinimo());
                    prep.setDouble(9, catalogoDetalle.getRangoMaximo());
                    prep.setInt(10, catalogoDetalle.getAnio());
                    prep.setDouble(11, catalogoDetalle.getValor());
                    prep.setLong(12, catalogoDetalle.getId());
                    prep.addBatch();
                    prep.executeBatch();
                    Conexion.getConection().setAutoCommit(true);
                }
                Conexion.getConection().close();
            }
        } catch (SQLException e) {
            Logger.getLogger(CatalogoDetalleServicio.class.getName()).log(Level.SEVERE, null, e);
        }
    }

    public boolean eliminar(Long id) {
        boolean ok = false;
        try {
            if (Conexion.getConection() != null) {
                String sql = " UPDATE catalogo_detalle "
                        + " SET ESTADO = 0 WHERE ID = ? ";
                try (PreparedStatement prep = Conexion.getConection().prepareStatement(sql)) {
                    prep.setLong(1, id);
                    prep.addBatch();
                    prep.executeBatch();
                    Conexion.getConection().setAutoCommit(true);
                    ok = true;
                }
                Conexion.getConection().close();
            }
        } catch (SQLException e) {
            Logger.getLogger(CatalogoDetalleServicio.class.getName()).log(Level.SEVERE, null, e);
        }
        return ok;
    }
    
}
